import cv2
import numpy as np
import matplotlib.pyplot as plt
from math import sqrt
from xml.dom.minidom import parse
import pytesseract
import os


def xml_read(xml_read_path):
    """
    该函数作用：解析表格结构信息
    :param xml_read_path: excel表格xml文件的存放位置
    :return: All_cell_points, Rows, sum(col_list), sum(height_list)
    """
    Index = 0
    # XML文件的读取
    dom = parse(xml_read_path)
    # 获取文档元素对象
    data = dom.documentElement
    # 读取sheet
    sheet = data.getElementsByTagName('Worksheet')
    # 读取 table
    table = sheet[0].getElementsByTagName('Table')
    # 读取table中的默认参数
    # 默认行高
    de_height = float(table[0].getAttribute("ss:DefaultRowHeight"))
    # 默认列宽
    de_width = float(table[0].getAttribute("ss:DefaultColumnWidth"))
    # 默认行数
    row_num = int(table[0].getAttribute("ss:ExpandedRowCount"))
    # 默认列数
    col_num = int(table[0].getAttribute("ss:ExpandedColumnCount"))
    # 读取列宽
    # Column = table[0].getElementsByTagName('Column')
    # 读取列宽
    # 首先读取所有的列宽元素
    Column = table[0].getElementsByTagName('Column')
    col_list = col_num * [de_width]
    # 列宽元素的索引
    i = 0
    for col in Column:
        if col.getAttribute("ss:Span") != '' and col.getAttribute("ss:Index") != '':
            index = int(col.getAttribute("ss:Index"))
            span = int(col.getAttribute("ss:Span"))
            width = float(col.getAttribute("ss:Width"))
            col_list[index - 1:index + span] = [width] * (span + 1)
            i = index + span
        elif col.getAttribute("ss:Span") == '' and col.getAttribute("ss:Index") != '':
            index = int(col.getAttribute("ss:Index"))
            width = float(col.getAttribute("ss:Width"))
            col_list[index - 1] = width
            i = index
        elif col.getAttribute("ss:Span") == '' and col.getAttribute("ss:Index") == '':
            width = float(col.getAttribute("ss:Width"))
            col_list[i] = width
            i = i + 1
            # print(width, i)

    # 读取所有的行信息
    Rows = table[0].getElementsByTagName('Row')
    # 读取每一行行高
    height_list = []
    for row in Rows:
        # height_list.append(float(row.getAttribute("ss:Height")))
        if row.getAttribute("ss:Height") == '':
            height_list.append(float(de_height))
        else:
            height_list.append(float(row.getAttribute("ss:Height")))

    # 以左上顶点为原点，各个单元的位置关系,向下、向右为正方向
    All_cell_points = []
    # 坐标的行列信息【【cell_col, cell_row】, ...】,为后续的表格写入做准备
    cells_adress = []
    cell_col = []
    cell_row = []
    # 同时获取表格的结构信息
    for index in range(len(Rows)):
        # 获取每一行中的单元格并计算
        cells = Rows[index].getElementsByTagName('Cell')
        #     print()
        #     print(index+1,'行：')

        # i : cell索引
        i = 0
        cell_col = []
        cell_row = []

        height_start = 0
        width_start = 0
        height_end = 0
        width_end = 0
        # 按行记录每个单元格的位置信息
        row_cell_points = []
        for index_cell, cell in enumerate(cells):
            # print('每行的单元格的次序：',index_cell+1)
            # print()
            # 计算得到单元格的起始单元格位置
            # 计算列宽
            #  ss:Index 一般是合并单元格从第一个开始就会出现ss:Index或者是某个纵向合并单元格之后多行的合并单元格
            # ，其后面的数字是在原本一个的基础上又合并的单元格数量

            # 这是该行的第一个单元格不是从最边上开始的且不是合并单元格
            if cell.getAttribute("ss:Index") and cell.getAttribute('ss:MergeAcross') == '':
                # print("ss:Index:")
                cell_col = []
                cell_row = []
                i = int(cell.getAttribute("ss:Index")) - 1
                cell_col.append(i)
                width = col_list[i]
                width_start = sum(col_list[:i])
                width_end = width + width_start

                cell_width = [width_start, width_end]
                # print('width:',cell_width)
                i = i + 1
                cell_col.append(i)

            # 合并单元格的数量和方向
            # MergeAcross横向合并，其后面的数字是在原来一个的基础上合并了单元格的数量
            # #这是该行的第一个单元格不是从最边上开始的而且是合并单元格
            elif cell.getAttribute("ss:Index") and cell.getAttribute('ss:MergeAcross'):
                # print("ss:Index:ss::Merge")
                cell_col = []
                cell_row = []
                i = i + int(cell.getAttribute("ss:Index")) - 1
                merge_col = int(cell.getAttribute("ss:MergeAcross"))
                cell_col.append(i)
                width = sum(col_list[i:i + merge_col + 1])
                width_start = sum(col_list[:i])
                width_end = width + width_start
                cell_width = [width_start, width_end]
                # print('width:',cell_width)

                cell_col.append(i)
                i = i + merge_col + 1
                cell_col.append(i)
            # 按照正常顺序合并的单元格
            elif cell.getAttribute("ss:MergeAcross"):
                # print("ss:MergeAcross:")
                cell_col = []
                cell_row = []
                merge_col = int(cell.getAttribute("ss:MergeAcross"))
                # 存放每个单元格起始和结束的位置
                width = sum(col_list[i:i + merge_col + 1])
                width_start = sum(col_list[:i])
                width_end = width + width_start
                cell_width = [width_start, width_end]

                # print('width:',cell_width)

                cell_col.append(i)
                i = i + merge_col + 1
                cell_col.append(i)

            # 正常的单元格
            elif cell.getAttribute("ss:Index") == '' and cell.getAttribute("ss:MergeAcross") == '':
                # print("ss:IN ss:m ==   ")
                cell_col = []
                cell_row = []

                # print(i)

                width = col_list[i]
                cell_col.append(i)
                width_start = sum(col_list[:i])
                width_end = width + width_start
                cell_width = [width_start, width_end]
                i = i + 1
                cell_col.append(i)

            # 计算单元格的行高
            if cell.getAttribute("ss:MergeDown"):
                merge_row = int(cell.getAttribute("ss:MergeDown"))
                height = sum(height_list[index:index + merge_row + 1])
                height_start = sum(height_list[:index])
                height_end = height_start + height
                cell_height = [height_start, height_end]

                # print('height:',cell_height)

                cell_row.append(index)
                cell_row.append(index + merge_row + 1)

            elif cell.getAttribute("ss:MergeDown") == '':
                height_start = sum(height_list[:index])
                height = height_list[index]
                height_end = height_start + height
                cell_height = [height_start, height_end]

                #  print('height:',cell_height)

                cell_row.append(index)
                cell_row.append(index + 1)

            # 存放单元格左上和右下角点的坐标
            cell_points = [[height_start, width_start], [height_end, width_end], Index]

            row_cell_points.append(cell_points)

            # 存放每个单元格的行列位置信息
            cells_adress.append([cell_col, cell_row])

            Index = Index + 1

        All_cell_points.append(row_cell_points)

    return All_cell_points, Rows, sum(col_list), sum(height_list)
